package xupt.se.ttms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import xupt.se.ttms.idao.iSeatDAO;
import xupt.se.ttms.model.Order;
import xupt.se.ttms.model.Sale;
import xupt.se.ttms.model.Seat;
import xupt.se.util.MyDBUtil;

public class SeatDAO implements iSeatDAO{
	private final String insertSql="insert into T_Seat(studio_id,row,col,state)values(?,?,?,?)";
	//通过主键id 删除
	private final String deleteSql="delete from T_Seat where seat_id=?";	
	
	private final String updateSql="update T_Seat set studio_id=?,row=?,col=?,state=? where seat_id=?";
	//通过主键id查询
	private final String selectSqlInt="select * from T_Seat where seat_id=?";
	
	//查询全部
	private final String selectSqlAll="select * from T_Seat";
	//通过studio_id 查询Seat数据库
	private final String selectSqlStudio_id="select * from T_Seat where studio_id=?";
	//查询损坏的座位
	private final String selectSqlBadSeat="select * from T_Seat where state=?" ;
	//初始化演出厅studio_id的座位
	private final String deleteinit="delete from T_Seat where studio_id=?";
	//清空座位
	private final String deleteAll="delete  from T_Seat ";
	
	private final String selectMore="select * from T_Seat where studio_id=? and row=? and col=?";
	@Override
	public boolean execInsert(Seat seat) throws Exception {
		String sql=insertSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			//ps.setInt(1,customer.);
			
			ps.setInt(1,seat.getStudio_id());
			ps.setInt(2,seat.getRow());
			ps.setFloat(3,seat.getCol());
			ps.setString(4, seat.getState());

			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean execUpdate(Seat seat) throws Exception {
		String sql=updateSql;
		Connection conn=null;
		boolean flag=false;
		PreparedStatement ps=null;
		MyDBUtil db = new MyDBUtil();
		conn=db.openConnection();
		try {
			if(null==conn)
				throw new Exception("Database not connected!");
			
			ps=conn.prepareStatement(sql);
			//ps.setInt(1,customer.);
			
			ps.setInt(1,seat.getStudio_id());
			ps.setInt(2,seat.getRow());
			ps.setFloat(3,seat.getCol());
			ps.setString(4, seat.getState());
			ps.setInt(5, seat.getSeat_id());
			flag=ps.execute();
			/*
			Statement stmt = conn.createStatement();
			flag = stmt.execute(sql);*/			
			ps.close();	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public List<Seat> HandleSeatResultSet(ResultSet rs) throws SQLException {
		 //结果，在第一个查询结果有个rs标记，通过rs.next()遍历获取全部
		Seat seat=null;
		List<Seat> seats=new ArrayList<>();
		 //获取结果的数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取该条结果有多少个列数据
        int columnCount = rsmd.getColumnCount();
        
       
    	System.out.println("获取演出计划数据中");
    	//通过rs.next()遍历获取全部结果
        while (rs.next()) {
        
        	seat=new Seat();
       	 //输出每条结果的全部数据
           for (int i = 0; i < columnCount; i++) {
        	 
        	   seat.setStudio_id(rs.getInt("studio_id"));
        	   seat.setRow(rs.getInt("row"));
        	   seat.setCol(rs.getInt("col"));
        	   seat.setSeat_id(rs.getInt("seat_id"));
        	   seat.setState(rs.getString("state"));
        
           }
           seats.add(seat);
           System.out.println();		                    
        }
    
        return seats;
	}

	@Override
	public ResultSet execQuery(int i) throws Exception {
		String sql=selectSqlInt;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, i);
		return rstSet;
	}

	@Override
	public ResultSet exexQueryAll() throws Exception {
		String sql=selectSqlAll;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execAllQuery(sql);
		return rstSet;
	}

	@Override
	public boolean execDelete(int i) throws Exception {
		String sql=deleteSql;
		boolean flag = false;
		MyDBUtil myDBUtil=new MyDBUtil();
		flag=myDBUtil.execDelete(sql, i);
		return flag;
	}

	@Override
	public ResultSet execStudio_id(int studio_id) throws Exception {
		String sql=selectSqlStudio_id;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, studio_id);
		return rstSet;
	}

	@Override
	public ResultSet execQuery(String state) throws Exception {
		String sql=selectSqlBadSeat;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuery(sql, state);
		return rstSet;
	}

	@Override
	public boolean execInit(int studio_id) throws Exception {
		String sql=deleteinit;
		boolean flag = false;
		MyDBUtil myDBUtil=new MyDBUtil();
		flag=myDBUtil.execDelete(sql, studio_id);
		return flag;
	}

	@Override
	public boolean execDeleteAll() throws Exception {
		
			String sql=deleteSql;
			boolean flag = false;
			MyDBUtil myDBUtil=new MyDBUtil();
			flag=myDBUtil.execDeleteAll(sql);
			return flag;
		
	}
	@Override
	public ResultSet execQuery(int studio_id,int row,int col) throws Exception {
		String sql=selectMore;
		ResultSet rstSet = null;
		MyDBUtil myDBUtil=new MyDBUtil();
		rstSet=myDBUtil.execQuerySeat(sql, studio_id,row,col);
		return rstSet;
	}
}
